/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.ipo.mm.ejb.util;

/**
 *
 * @author makcro
 */
public class MeetingSearchQueryBuilder {

    private boolean meetingClauseAdded = false;
    private boolean orgClauseAdded = false;
    private boolean repClauseAdded = false;
    private StringBuilder query;
    private final String meetingNameFilter;
    private final String meetingNameOper;
    private final String venueFilter;
    private final String venueOper;
    private final String conclusionsFilter;
    private final String conclusionsOper;
    private final String dateInterval;
    private final String orgNameFilter;
    private final String orgNameOper;
    private final String[] orgInstFilter;
    private final String[] orgITypeFilter;
    private final String[] orgIFamilyFilter;
    private final String[] orgIRegionFilter;
    private final String repNameFilter;
    private final String repNameOper;
    private final String[] repInstFilter;
    private final String[] repITypeFilter;
    private final String[] repIFamilyFilter;
    private final String[] repIRegionFilter;
    private final String orderByFilter;
    private final boolean isCount;

    public MeetingSearchQueryBuilder(String meetingNameFilter,
            String meetingNameOper,
            String venueFilter,
            String venueOper,
            String conclutionsFilter,
            String conclutionsOper,
            String dateInterval,

            String orgNameFilter,
            String orgNameOper,

            String[] orgInstFilter,
            String[] orgITypeFilter,
            String[] orgIFamilyFilter,
            String[] orgIRegionFilter,

            String repNameFilter,
            String repNameOper,

            String[] repInstFilter,
            String[] repITypeFilter,
            String[] repIFamilyFilter,
            String[] repIRegionFilter,

            String orderByFilter,
            boolean isCount) {
        this.meetingNameFilter = meetingNameFilter;
        this.meetingNameOper = meetingNameOper;
        this.venueFilter = venueFilter;
        this.venueOper = venueOper;
        this.conclusionsFilter = conclutionsFilter;
        this.conclusionsOper = conclutionsOper;
        this.dateInterval = dateInterval;
        this.orgNameFilter = orgNameFilter;
        this.orgNameOper = orgNameOper;
        this.orgInstFilter = orgInstFilter;
        this.orgITypeFilter = orgITypeFilter;
        this.orgIFamilyFilter = orgIFamilyFilter;
        this.orgIRegionFilter = orgIRegionFilter;
        this.repNameFilter = repNameFilter;
        this.repNameOper = repNameOper;
        this.repInstFilter = repInstFilter;
        this.repITypeFilter = repITypeFilter;
        this.repIFamilyFilter = repIFamilyFilter;
        this.repIRegionFilter = repIRegionFilter;
        this.orderByFilter = orderByFilter;
        this.isCount = isCount;

        if (isCount) {
            this.query = new StringBuilder("SELECT COUNT(*) FROM MEETING AS m WHERE m.Deleted = 0 ");
        } else {
            this.query = new StringBuilder("SELECT * FROM MEETING AS m WHERE m.Deleted = 0 ");
        }
    }

    public String getSearchQuery() {
        ///event name
        if (this.meetingNameFilter != null) {
            //if (!this.meetingClauseAdded) {
            addMeetingClause();
            //}
            //array of name filter words
            query.append("\n(");

            String eventNameExpresion = buildSimpleCondition(meetingNameFilter,
                    meetingNameOper,
                    "m",
                    "EventName");

            query.append(eventNameExpresion);
            query.append(")\n");
            printQuery();
        }
        //event venue
        if (this.venueFilter != null) {
            if (!meetingClauseAdded) {
                addMeetingClause();
            } else {
                addAndClause();
            }


            //array of name filter words
            query.append("\n(");

            String eventNameExpresion = buildSimpleCondition(venueFilter,
                    venueOper,
                    "m",
                    "Venue");

            query.append(eventNameExpresion);
            query.append(")\n");

            printQuery();
        }
        //conclusion
        if (this.conclusionsFilter != null) {
            if (!meetingClauseAdded) {
                addMeetingClause();
            } else {
                addAndClause();
            }

            query.append("\n(");

            String eventNameExpresion = buildSimpleCondition(conclusionsFilter,
                    conclusionsOper,
                    "m",
                    "Conclusion");

            query.append(eventNameExpresion);
            query.append(")\n");

            printQuery();
        }






        //date interval
        if (dateInterval != null) {
            if (!meetingClauseAdded) {
                addMeetingClause();
            } else {
                addAndClause();
            }

            query.append("\n(");
            String[] intervalEnds = dateInterval.split(" ");
            String expression = " m.Date BETWEEN '" + intervalEnds[0] + "' AND '" + intervalEnds[1] + "'";


            query.append(expression);
            query.append(")\n");

            printQuery();
        }
        /**************ORGANIZER**************/
        if (orgNameFilter != null) {
            if (!orgClauseAdded) {
                addOrganizerClause();
            } else {
                addAndClause();
            }

            query.append("\n(");

            String expresion = buildNameCondition(orgNameFilter, orgNameOper);

            query.append(expresion);
            query.append(")\n");
            printQuery();

        }

        if (orgInstFilter != null) {
            if (!orgClauseAdded) {
                addOrganizerClause();
            } else {
                addAndClause();
            }

            query.append(" i.ID " + buildSetCondition(this.orgInstFilter));
            printQuery();
        }

        if (orgIFamilyFilter != null) {
            if (!orgClauseAdded) {
                addOrganizerClause();
            } else {
                addAndClause();
            }

            query.append(" f.ID " + buildSetCondition(this.orgIFamilyFilter));
            printQuery();
        }

        if (orgITypeFilter != null) {
            if (!orgClauseAdded) {
                addOrganizerClause();
            } else {
                addAndClause();
            }

            query.append(" t.ID " + buildSetCondition(this.orgITypeFilter));
            printQuery();
        }

        if (orgIRegionFilter != null) {
            if (!orgClauseAdded) {
                addOrganizerClause();
            } else {
                addAndClause();
            }

            query.append(" reg.ID " + buildSetCondition(this.orgIRegionFilter));
            printQuery();
        }

        if (orgClauseAdded) {
            query.append(")");
            printQuery();
        }

        /**************REPRESENTATIVES******************/
        if (repNameFilter != null) {
            if (!repClauseAdded) {
                addRepresentativeClause();
            } else {
                addAndClause();
            }

            query.append("\n(");

            String expresion = buildNameCondition(repNameFilter, repNameOper);

            query.append(expresion);
            query.append(")\n");
            printQuery();

        }

        if (repInstFilter != null) {
            if (!repClauseAdded) {
                addRepresentativeClause();
            } else {
                addAndClause();
            }

            query.append(" i.ID " + buildSetCondition(this.repInstFilter));
            printQuery();
        }

        if (repIFamilyFilter != null) {
            if (!repClauseAdded) {
                addRepresentativeClause();
            } else {
                addAndClause();
            }

            query.append(" f.ID " + buildSetCondition(this.repIFamilyFilter));
            printQuery();
        }

        if (repITypeFilter != null) {
            if (!repClauseAdded) {
                addRepresentativeClause();
            } else {
                addAndClause();
            }

            query.append(" t.ID " + buildSetCondition(this.repITypeFilter));
            printQuery();
        }

        if (repIRegionFilter != null) {
            if (!repClauseAdded) {
                addRepresentativeClause();
            } else {
                addAndClause();
            }

            query.append(" reg.ID " + buildSetCondition(this.repIRegionFilter));
            printQuery();
        }

        if (repClauseAdded) {
            query.append(")");
            printQuery();
        }

        if (meetingClauseAdded) {
            query.append(")");
            printQuery();
        }

        if (orderByFilter != null) {
            query.append("\n ORDER BY m." + orderByFilter);
            printQuery();
        }


        return query.toString();
    }

    private String buildSetCondition(String[] filter) {
        String expresion = " IN (";
        for (int i = 0; i < filter.length; i++) {
            String value = filter[i];
            expresion += value;
            if (i != filter.length - 1) {
                expresion += ", ";
            }

        }
        expresion += ")";

        return expresion;
    }

    private void addMeetingClause() {
        String meetigClause = " AND (";
        query.append(meetigClause);
        meetingClauseAdded = true;
    }

    private String buildNameCondition(String filter,
            String filterOper) {

        String expresion = "";
        String[] filters = filter.split(" ");
        for (int i = 0; i < filters.length; i++) {
            String someFilter = filters[i];

            expresion += " (r.FirstName LIKE('%" + someFilter + "%') OR r.LastName LIKE ('%" + someFilter + "%')) ";

            if (i != filters.length - 1) {
                expresion += " " + filterOper + " ";
            }
        }

        return expresion;


    }

    private void addOrganizerClause() {
        String orgClause_normal = "\n AND m.ID IN ( \n" +
                " SELECT m.ID FROM MEETING AS m, REPRESENTATIVE AS r, INSTITUTION AS i, INSTITUTION_FAMILY AS f, INSTITUTION_REGION AS reg, INSTITUTION_TYPE AS t \n" +
                " WHERE m.Organizer = r.ID AND i.ID = r.Institution AND f.ID = i.Family AND reg.ID = i.Region AND t.ID = i.Type \n" +
                " AND \n";

        String orgClause_spec = "\n m.ID IN ( \n" +
                " SELECT m.ID FROM MEETING AS m, REPRESENTATIVE AS r, INSTITUTION AS i, INSTITUTION_FAMILY AS f, INSTITUTION_REGION AS reg, INSTITUTION_TYPE AS t \n" +
                " WHERE m.Organizer = r.ID AND i.ID = r.Institution AND f.ID = i.Family AND reg.ID = i.Region AND t.ID = i.Type \n" +
                " AND \n";

        if (!meetingClauseAdded) {
            addMeetingClause();
            query.append(orgClause_spec);
        } else {
            query.append(orgClause_normal);

        }
        orgClauseAdded = true;

    }

    private void addRepresentativeClause() {
        String orgClause_normal = "\n AND m.ID IN ( " +
                " SELECT j.Meeting FROM REPRESENTATIVE_MEETING_JOIN AS j, REPRESENTATIVE AS r, INSTITUTION AS i, INSTITUTION_FAMILY AS f, INSTITUTION_REGION AS reg, INSTITUTION_TYPE AS t \n" +
                " WHERE j.Representative = r.ID AND i.ID = r.Institution AND f.ID = i.Family AND reg.ID = i.Region AND t.ID = i.Type \n" +
                " AND \n";

        String orgClause_spec = "\n m.ID IN ( " +
                " SELECT j.Meeting FROM REPRESENTATIVE_MEETING_JOIN AS j, REPRESENTATIVE AS r, INSTITUTION AS i, INSTITUTION_FAMILY AS f, INSTITUTION_REGION AS reg, INSTITUTION_TYPE AS t \n" +
                " WHERE j.Representative = r.ID AND i.ID = r.Institution AND f.ID = i.Family AND reg.ID = i.Region AND t.ID = i.Type \n" +
                " AND \n";

        if (!meetingClauseAdded) {
            addMeetingClause();
            query.append(orgClause_spec);
        } else {
            query.append(orgClause_normal);
        }

        repClauseAdded = true;
    }

    private void addAndClause() {
        String andClause = " AND ";
        query.append(andClause);
    }

    private String buildSimpleCondition(String filter,
            String filterOper,
            String entity,
            String attribute) {

        String expresion = "";
        String[] filters = filter.split(" ");



        for (int i = 0; i < filters.length; i++) {
            String someFilter = filters[i];
            expresion += " " + entity + "." + attribute + " LIKE('%" + someFilter + "%') ";
            if (i != filters.length - 1) {
                expresion += " " + filterOper + " ";
            }

        }
        return expresion;

    }

    private void printQuery() {
        System.out.println(query.toString());
        System.out.println("==============================================");
    }
}
